/*
 * Este programa es software libre; usted puede redistribuirlo y/o modificarlo bajo los terminos
 * de la licencia "GNU General Public License" publicada por la Fundacion "Free Software Foundation".
 * Este programa se distribuye con la esperanza de que pueda ser util, pero SIN NINGUNA GARANTIA;
 * vea la licencia "GNU General Public License" para obtener mas informacion.
 */
package adalid.util.sql;

import adalid.commons.properties.PropertiesGetter;
import adalid.commons.velocity.Writer;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import org.apache.commons.collections.ExtendedProperties;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

/**
 * @author Jorge Campins
 */
public class SqlMerger extends SqlUtil {

    private static final Logger logger = Logger.getLogger(SqlMerger.class);

    public static synchronized void main(String[] args) {
        if (args.length == 0) {
            ExtendedProperties properties = PropertiesGetter.getPrivateProperties();
            args = properties.getStringArray(SqlMerger.class.getName() + ".args");
        }
        SqlMerger merger = new SqlMerger(args);
        if (merger.isInitialised()) {
            merger.merge();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="instance fields">
    private String _oldSchema;

    private boolean _testingPhase;

    private SqlReader _reader1, _reader2;

    private Map<String, String> _tablesLoadMap = new LinkedHashMap<>();

    private Set<String> _tableNames = new TreeSet<>();

    private List<SqlTable> _addedTables = new ArrayList<>();

    private List<SqlTable> _droppedTables = new ArrayList<>();

    private Map<String, SqlColumn> _newColumns = new LinkedHashMap<>();

    private Map<String, SqlColumn> _oldColumns = new LinkedHashMap<>();

    private List<SqlColumn> _addedColumns = new ArrayList<>();

    private List<SqlColumn> _droppedColumns = new ArrayList<>();

    private Map<String, List<String>> _warnings = new LinkedHashMap<>();
    // </editor-fold>

    public SqlMerger(String[] args) {
        super(args);
        _initialised = _initialised && oldSchema(args);
    }

    // <editor-fold defaultstate="collapsed" desc="args">
    private boolean oldSchema(String[] args) {
        _oldSchema = arg(7, args);
        if (StringUtils.isNotBlank(_oldSchema)) {
            logValidArgument("old schema", _oldSchema);
            return true;
        }
        logInvalidArgument("old schema", _oldSchema);
        logSyntaxError();
        return false;
    }

    @Override
    protected void logSyntaxError() {
        logger.error("Syntax: " + getClass().getSimpleName() + " dbms, host, port, user, password, database, schema, oldSchema");
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="instance getters and setters">
    public boolean isTestingPhase() {
        return _testingPhase;
    }

    public String getNewSchema() {
        return _schema;
    }

    public String getOldSchema() {
        return _oldSchema;
    }

    public Set<String> getTableNames() {
        return _tableNames;
    }

    public List<SqlTable> getAddedTables() {
        return _addedTables;
    }

    public List<SqlTable> getDroppedTables() {
        return _droppedTables;
    }

    public Map<String, SqlColumn> getNewColumns() {
        return _newColumns;
    }

    public Map<String, SqlColumn> getOldColumns() {
        return _oldColumns;
    }

    public Set<String> getModifiedColumnNames() {
        return _newColumns.keySet();
    }

    public List<SqlColumn> getAddedColumns() {
        return _addedColumns;
    }

    public List<SqlColumn> getDroppedColumns() {
        return _droppedColumns;
    }

    public Map<String, List<String>> getWarnings() {
        return _warnings;
    }
    // </editor-fold>

    public synchronized boolean merge() {
        return merge(false);
    }

    public synchronized boolean merge(boolean testing) {
        logger.info("merge");
        _testingPhase = testing;
        _tableNames.clear();
        _addedTables.clear();
        _droppedTables.clear();
        _newColumns.clear();
        _oldColumns.clear();
        _addedColumns.clear();
        _droppedColumns.clear();
        boolean merge = _initialised;
        merge = merge && reader1();
        merge = merge && reader2();
        merge = merge && _reader1.read();
        merge = merge && _reader2.read();
        merge = merge && mergeTables();
        merge = merge && write();
        return merge;
    }

    private boolean reader1() {
        String[] args = new String[]{_dbms, _host, _port, _user, _password, _database, _schema};
        _reader1 = new SqlReader(args);
        _reader1.setSelectTemplatesPath("templates/meta/sql");
        _reader1.setTablesLoadMap(_tablesLoadMap);
        return _reader1.isInitialised();
    }

    private boolean reader2() {
        String[] args = new String[]{_dbms, _host, _port, _user, _password, _database, _oldSchema};
        _reader2 = new SqlReader(args);
        _reader2.setSelectTemplatesPath("templates/meta/sql");
        _reader2.setTablesLoadMap(_tablesLoadMap);
        return _reader2.isInitialised();
    }

    /**
     * @return the tables load set
     */
    public Set<String> getTablesLoadSet() {
        return _tablesLoadMap.keySet();
    }

    /**
     * @return the tables load map
     */
    public Map<String, String> getTablesLoadMap() {
        return _tablesLoadMap;
    }

    /**
     * @param map the tables load map to set
     */
    public void setTablesLoadMap(Map<String, String> map) {
        _tablesLoadMap = map;
    }

    private boolean mergeTables() {
        Map<String, SqlTable> map1 = _reader1.getTablesMap();
        Map<String, SqlTable> map2 = _reader2.getTablesMap();
        _tableNames.addAll(map1.keySet());
        _tableNames.addAll(map2.keySet());
        for (String key : _tableNames) {
            if (map1.containsKey(key) && map2.containsKey(key)) {
                mergeColumns(map1.get(key), map2.get(key));
            } else if (map1.containsKey(key)) {
                createTable(map1.get(key));
            } else {
                dropTable(map2.get(key));
            }
        }
        return true;
    }

    private boolean mergeColumns(SqlTable table1, SqlTable table2) {
        Map<String, SqlColumn> map1 = table1.getColumnsMap();
        Map<String, SqlColumn> map2 = table2.getColumnsMap();
        Set<String> keySet = new TreeSet<>();
        keySet.addAll(map1.keySet());
        keySet.addAll(map2.keySet());
        for (String key : keySet) {
            if (map1.containsKey(key) && map2.containsKey(key)) {
                modifyColumn(map1.get(key), map2.get(key));
            } else if (map1.containsKey(key)) {
                addColumn(map1.get(key));
            } else {
                dropColumn(map2.get(key));
            }
        }
        if (table1.isLoaded() && table2.isLoaded()) {
            compareRows(table1, table2, keySet);
        }
        return true;
    }

    private void compareRows(SqlTable table1, SqlTable table2, Set<String> colnames) {
        Map<String, SqlRow> map1 = table1.getRowsMap();
        Map<String, SqlRow> map2 = table2.getRowsMap();
        Set<String> keySet = new TreeSet<>();
        keySet.addAll(map1.keySet());
        keySet.addAll(map2.keySet());
        String tableName = table1.getName();
        String pk1 = table1.getPrimaryKey().getName();
        String pk2 = table2.getPrimaryKey().getName();
        SqlRow row1, row2;
        Map<String, SqlRowValue> valuesMap1, valuesMap2;
        SqlRowValue rowValue1, rowValue2;
        String value1, value2;
        String text;
        for (String key : keySet) {
            if (map1.containsKey(key) && map2.containsKey(key)) {
                row1 = map1.get(key);
                row2 = map2.get(key);
                valuesMap1 = row1.getValuesMap();
                valuesMap2 = row2.getValuesMap();
                for (String colname : colnames) {
                    rowValue1 = valuesMap1.get(colname);
                    rowValue2 = valuesMap2.get(colname);
                    value1 = rowValue1 == null ? null : rowValue1.getValue();
                    value2 = rowValue2 == null ? null : rowValue2.getValue();
                    if (StringUtils.equals(value1, value2)) {
                        continue;
                    }
                    text = warning(tableName, key, "modified") + ": " + colname + " \"" + value2 + "\" -> \"" + value1 + "\"";
                    if (colname.equals(pk1) || colname.equals(pk2)) {
                        add(tableName, text);
                        logger.warn(SqlUtil.highlight(text));
                    } else {
                        logger.info(text);
                    }
                }
            } else if (map1.containsKey(key)) {
                text = warning(tableName, key, "added");
                logger.info(text);
            } else {
                text = warning(tableName, key, "deleted");
                add(tableName, text);
                logger.warn(SqlUtil.highlight(text));
            }
        }
    }

    private void add(String name, String text) {
        List<String> list;
        if (_warnings.containsKey(name)) {
            list = _warnings.get(name);
        } else {
            list = new ArrayList<>();
            _warnings.put(name, list);
        }
        list.add(text);
    }

    private void createTable(SqlTable table) {
        String tableName = table.getName();
        String statement = "create table " + _oldSchema + "." + tableName;
        logger.info(statement);
        _addedTables.add(table);
        if (_testingPhase) {
            String name, type, defaultValue;
            int length, precision, scale;
            boolean nullable;
            Collection<SqlColumn> columns = table.getColumns();
            for (SqlColumn column : columns) {
                name = column.getName();
                type = column.getSqlDataType();
                length = column.getLength();
                precision = column.getPrecision();
                scale = column.getScale();
                nullable = column.isNullable();
                defaultValue = column.getSqlDefaultValue();
                statement = "\t" + name
                    + " [" + type + "(" + length + "," + precision + "," + scale + "," + nullable + "," + defaultValue + ")"
                    + "]";
                logger.info(statement);
            }
        }
    }

    private void dropTable(SqlTable table) {
        String tableName = table.getName();
        String statement = "drop table " + _oldSchema + "." + tableName + ";";
        logger.warn(SqlUtil.highlight(statement));
        _droppedTables.add(table);
    }

    private void modifyColumn(SqlColumn column1, SqlColumn column2) {
        if (equals(column1, column2)) {
            return;
        }
        String tableName = column1.getTable().getName();
        String columnName = column1.getName();
        String name = tableName + "." + columnName;
        /**/
        String type1 = column1.getSqlDataType();
        int length1 = column1.getLength();
        int precision1 = column1.getPrecision();
        int scale1 = column1.getScale();
        boolean nullable1 = column1.isNullable();
        String default1 = column1.getSqlDefaultValue();
        /**/
        String type2 = column2.getSqlDataType();
        int length2 = column2.getLength();
        int precision2 = column2.getPrecision();
        int scale2 = column2.getScale();
        boolean nullable2 = column2.isNullable();
        String default2 = column2.getSqlDefaultValue();
        /**/
        String statement = "alter table " + _oldSchema + "." + tableName + " modify column " + columnName
            + " [" + type2 + "(" + length2 + "," + precision2 + "," + scale2 + "," + nullable2 + "," + default2 + ")"
            + ", " + type1 + "(" + length1 + "," + precision1 + "," + scale1 + "," + nullable1 + "," + default1 + ")"
            + "];";
        if (type1.equals(type2) && (length1 == 0 || (length1 >= length2 && length2 > 0)) && precision1 >= precision2) {
            logger.info(statement);
        } else {
            logger.warn(SqlUtil.highlight(statement));
        }
        _newColumns.put(name, column1);
        _oldColumns.put(name, column2);
    }

    private void addColumn(SqlColumn column) {
        String tableName = column.getTable().getName();
        String columnName = column.getName();
        String type = column.getSqlDataType();
        int length = column.getLength();
        int precision = column.getPrecision();
        int scale = column.getScale();
        boolean nullable = column.isNullable();
        String defaultValue = column.getSqlDefaultValue();
        String statement = "alter table " + _oldSchema + "." + tableName + " add column " + columnName
            + " [" + type + "(" + length + "," + precision + "," + scale + "," + nullable + "," + defaultValue + ")"
            + "];";
        logger.info(statement);
        _addedColumns.add(column);
    }

    private void dropColumn(SqlColumn column) {
        String tableName = column.getTable().getName();
        String columnName = column.getName();
        String statement = "alter table " + _oldSchema + "." + tableName + " drop column " + columnName + ";";
        logger.warn(SqlUtil.highlight(statement));
        _droppedColumns.add(column);
    }

    private String warning(String tableName, String key, String adjective) {
        return tableName + " [" + key + "] " + adjective;
    }

    private boolean equals(SqlColumn column1, SqlColumn column2) {
        String type1 = column1.getSqlDataType();
        int length1 = column1.getLength();
        int precision1 = column1.getPrecision();
        int scale1 = column1.getScale();
        boolean nullable1 = column1.isNullable();
//      String default1 = column1.getSqlDefaultValue();
        /**/
        String type2 = column2.getSqlDataType();
        int length2 = column2.getLength();
        int precision2 = column2.getPrecision();
        int scale2 = column2.getScale();
        boolean nullable2 = column2.isNullable();
//      String default2 = column2.getSqlDefaultValue();
        /**/
        return type1.equals(type2)
            && length1 == length2
            && precision1 == precision2
            && scale1 == scale2
            && nullable1 == nullable2;
//          && StringUtils.equals(default1, default2);
    }

    private boolean write() {
        logger.info("write");
        Writer writer = new Writer(this, "merger");
        writer.write("meta-sql-" + _dbms);
        return true;
    }

}
